﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spPostDeploy_LookupTable_SqlSupport]
#-- Purpose:		List of support end dates for SQL Server.
#--	Last Update:	08/24/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spPostDeploy_LookupTable_SqlSupport]
AS

SET NOCOUNT ON

--- Build the list of versions
DECLARE @t TABLE (
	[verMajor] [varchar](10) NOT NULL,
	[verStart] [date] NOT NULL,
	[verRtmEnd] [date] NOT NULL,
	[verSp1End] [date] NULL,
	[verSp2End] [date] NULL,
	[verSp3End] [date] NULL,
	[verSp4End] [date] NULL,
	[verExtendedEnd] [date] NOT NULL
)
INSERT INTO @t ([verMajor], [verStart], [verRtmEnd], [verSp1End], [verSp2End], [verSp3End], [verSp4End], [verExtendedEnd])
	SELECT '8', '11/30/2000', '07/11/2002', '02/28/2002', '04/07/2003', '07/10/2007', '04/08/2008', '04/09/2013'
	UNION SELECT '9', '01/14/2006', '07/10/2007', '04/08/2008', '01/12/2010', '01/10/2012', '04/12/2011', '04/12/2016'
	UNION SELECT '10.0', '11/07/2008', '04/13/2010', '10/11/2011', '10/09/2012', '07/08/2014', '07/08/2014', '07/09/2019'
	UNION SELECT '10.5', '11/07/2008', '07/10/2012', '10/08/2013', '07/08/2014', '07/08/2014', NULL, '07/09/2019'
	UNION SELECT '11', '05/20/2012', '01/14/2014', '07/14/2015', '01/10/2017', '07/11/2017', NULL, '07/12/2022'
	UNION SELECT '12', '06/05/2014', '07/12/2016', '10/10/2017', '07/09/2019', NULL, NULL, '07/09/2024'
	UNION SELECT '13', '06/01/2016', '07/13/2021', NULL, NULL, NULL, NULL, '07/14/2026'

--- Update Changed Records
UPDATE		s
SET			[verStart] = t.[verStart],
			[verRtmEnd] = t.[verRtmEnd],
			[verSp1End] = t.[verSp1End],
			[verSp2End] = t.[verSp2End],
			[verSp3End] = t.[verSp3End],
			[verSp4End] = t.[verSp4End],
			[verExtendedEnd] = t.[verExtendedEnd]
FROM		@t t
JOIN		[dbo].[tblMSX_sql_support] s ON t.[verMajor] = s.[verMajor]
			AND NOT (
				ISNULL(t.[verStart], '01/01/1900') = ISNULL(s.[verStart], '01/01/1900')
				AND ISNULL(t.[verRtmEnd], '01/01/1900') = ISNULL(s.[verRtmEnd], '01/01/1900')
				AND ISNULL(t.[verSp1End], '01/01/1900') = ISNULL(s.[verSp1End], '01/01/1900')
				AND ISNULL(t.[verSp2End], '01/01/1900') = ISNULL(s.[verSp2End], '01/01/1900')
				AND ISNULL(t.[verSp3End], '01/01/1900') = ISNULL(s.[verSp3End], '01/01/1900')
				AND ISNULL(t.[verSp4End], '01/01/1900') = ISNULL(s.[verSp4End], '01/01/1900')
				AND ISNULL(t.[verExtendedEnd], '01/01/1900') = ISNULL(s.[verExtendedEnd], '01/01/1900')
			)

--- Insert Missing Records
INSERT INTO [dbo].[tblMSX_sql_support] ([verMajor], [verStart], [verRtmEnd], [verSp1End], [verSp2End], [verSp3End], [verSp4End], [verExtendedEnd])
	SELECT		t.[verMajor],
				t.[verStart],
				t.[verRtmEnd],
				t.[verSp1End],
				t.[verSp2End],
				t.[verSp3End],
				t.[verSp4End],
				t.[verExtendedEnd]
	FROM		@t t
	LEFT JOIN	[dbo].[tblMSX_sql_version] s ON t.[verMajor] = s.[verMajor]
	WHERE		s.[verMajor] IS NULL

SET NOCOUNT OFF